package com.android.pI;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper {

	//default
	private static String dbPath = "/data/data/com.android.pI/databases/";
	private static String dbName = "pI";
	private SQLiteDatabase theDB;
	private final Context myContext;

	// Constructor Takes and keeps a reference of the passed context
	public DataBaseHelper(Context context) {
		super(context, dbName, null, 1);
		this.myContext = context;
	}

	// Creates empty db the system and rewrites to db from assets folder
	public void createDataBase() throws IOException {
		boolean dbExist = checkDataBase();
		if (dbExist) {
			// do not overwrite an existing db
		} else {
			// overwrite that db with db from assets folder
			this.getReadableDatabase();
			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Can not copy database");
			}
		}
	}

	// Check if db has already been created
	private boolean checkDataBase() {
		SQLiteDatabase checkDB = null;
		try {
			String myPath = dbPath + dbName;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {
			// no db
		}
		if (checkDB != null) {
			checkDB.close();
			return true;
		} else
			return false;
	}

	 // copy db from asset folder to new empty db
	private void copyDataBase() throws IOException {

		// Open local db as input stream
		InputStream myInput = myContext.getAssets().open(dbName);

		// Path to new db
		String outFileName = dbPath + dbName;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		// Close streams
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public void openDataBase() throws SQLException {

		// Open the database
		String myPath = dbPath + dbName;
		theDB = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);
	}

	@Override
	public synchronized void close() {
		if (theDB != null)
			theDB.close();
		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	// Return a Cursor of all exercises
	public Cursor fetchWks() {
		openDataBase();
		Cursor mCursor = theDB.rawQuery("select distinct _id, wk_name from "
				+ "table_wk order by _id;", null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	// Return a Cursor of the exercises in given a workout
	public Cursor fetchExs(String workoutName) {
		openDataBase();
		Cursor mCursor = theDB.rawQuery(
				"select e._id, e.ex_name, e.ex_time, e.ex_image, MAX(l.reps) as reps "
						+ "from table_ex e, table_wk w "
						+ "LEFT OUTER JOIN table_log l "
						+ "ON e._id = l.ex_id " + "where e.wk_id = w._id "
						+ "AND w.wk_name = '" + workoutName + "' "
						+ "Group BY e._id " + "Order by e.ex_place ASC;", null);
		return mCursor;
	}
	

    public long createLog(String ex_id, String weight, String bands, String per, String notes, String date) {
		openDataBase();
        ContentValues initialValues = new ContentValues();
        initialValues.put("ex_id", ex_id);
        initialValues.put("weight", weight);
        initialValues.put("reps", per);
        initialValues.put("notes", notes);
        initialValues.put("date", date);
        initialValues.put("bands", bands);
        return theDB.insert("table_log", null, initialValues);
    }
    
	public Cursor fetchLog() {
		openDataBase();
		Cursor mCursor = theDB.rawQuery("select l._id, l.reps, e.ex_name, l.date " +
				"from table_log l, table_ex e " +
				"where l.ex_id = e._id " +
				"Order By l.Date DESC;", null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
    
	public Cursor fetchLogId(String rowId){
		openDataBase();
		Cursor mCursor = theDB.rawQuery("select l._id, l.reps, e.ex_name, l.date, l.weight, l.notes, l.bands " +
				"from table_log l, table_ex e " +
				"where l.ex_id = e._id And l._id = "+rowId+";", null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

    }

    public boolean UpdateLog(String rowId, String eid, String weight, String bands, String per, String notes, String date) {
		openDataBase();
        ContentValues args = new ContentValues();
        args.put("weight", weight);
        args.put("ex_id", eid);
        args.put("reps", per);
        args.put("notes", notes);
        args.put("date", date);
        args.put("bands", bands);
        return theDB.update("table_log", args, "_id = " + rowId, null) > 0;
    }
	public Cursor fetchAllExs() {
		openDataBase();
		Cursor mCursor = theDB.rawQuery("select distinct _id, ex_name from "
				+ "table_ex order by ex_name;", null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
    public long UpdaterEx(ContentValues values) {
		openDataBase();
        return theDB.insertWithOnConflict("table_ex", null, values,
                SQLiteDatabase.CONFLICT_REPLACE);
    }
}
